from ..utils.database import SessionLocal
from ..model.master import Material, Lot, AOI, Failure, FailureImage, PanelImage
from ..model.slave import AI
from sqlalchemy import and_, or_, func, desc
import json
import os
from datetime import datetime, timedelta
from ..config import Config, oracle_view
from fastapi import HTTPException
from ..utils.log import get_logger
from ..utils.database import connect_oracle, connect_sqlserver
import requests
import time
import pymssql
from .. import async_msg

root_path = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))

logger = get_logger(__name__) 


def get_material_name(material_code_str):
    with SessionLocal() as session:
        material_code_str = material_code_str
        material_codes = material_code_str.split(", ")
        material_names = []
        for material_code in material_codes:
            material = session.query(Material).filter_by(material_code=material_code).first()
            if material:
                material_name = material.material_name
                material_names.append(material_name)
        material_name_str = material_names[0] if len(material_names)==1 else ", ".join(material_names)

        return material_name_str
    

def get_aoi_name(aoi_code_str):
    with SessionLocal() as session:
        aoi_codes = aoi_code_str.split(", ")
        aoi_names = []
        for aoi_code in aoi_codes:
            aoi_name = session.query(AOI).filter_by(aoi_code=aoi_code).first().aoi_name
            aoi_names.append(aoi_name)
        aoi_name_str = aoi_names[0] if len(aoi_names)==1 else ", ".join(aoi_names)

        return aoi_name_str
    

def get_material_lots(material_names, lots=None):
    with SessionLocal() as session:
        conditions = []
        for material_name in material_names:
            materials = session.query(Material).filter_by(material_name=material_name).all()
            for material in materials:
                material_code = material.material_code
                conditions.append(Lot.material_code.like("%" + material_code + "%"))
        if conditions:
            if lots:
                lots = lots.filter(or_(*conditions))
            else:
                lots = session.query(Lot).filter(or_(*conditions))
        return lots
    

def get_material_lot(material_name, lots):
    with SessionLocal() as session:
        conditions = []
        materials = session.query(Material).filter_by(material_name=material_name).all()
        for material in materials:
            material_code = material.material_code
            conditions.append(Lot.material_code.like("%" + material_code + "%"))
        if conditions:
            if lots:
                lots = lots.filter(or_(*conditions))
            else:
                lots = session.query(Lot).filter(or_(*conditions))
        return lots

def panel_failure_data(panels, session, pattern_aoi=None, pattern_ai=None, lot_no=False):
    predict_data = []
    for panel_image in panels:
        lot = session.query(Lot).filter(Lot.lot_code == panel_image.lot_code).first()

        # panel 上去重的缺陷
        detection_info = json.loads(panel_image.detection_info) if panel_image.detection_info else []

        item = {}
        item["panel_id"] = panel_image.panel_id
        item["panel_code"] = panel_image.panel_code
        item["panel_aoi_time"] = panel_image.aoi_time.strftime('%Y-%m-%d %H:%M:%S') if panel_image.aoi_time else ""
        item["aoi_result"] = panel_image.aoi_result

        aoi_code = panel_image.aoi_code
        aoi = session.query(AOI).filter(AOI.aoi_code==aoi_code).first()
        if aoi:
            aoi_name = aoi.aoi_name
            item["aoi_name"] = aoi_name
        else:
            item["aoi_name"] = ""

        server_host = panel_image.server_host
        ai = session.query(AI).filter(AI.hostname==server_host).first()
        if ai:
            item["ai_name"] = ai.ai_name
        else:
            item["ai_name"] = ""

        if lot:
            item["lot_name"] = lot.lot_name
            item["lot_code"] = lot.lot_code
            item["start_time"] = panel_image.start_time.strftime('%Y-%m-%d %H:%M:%S') if panel_image.start_time else ""
            item["end_time"] = panel_image.end_time.strftime('%Y-%m-%d %H:%M:%S') if panel_image.end_time else ""
            material = session.query(Material).filter_by(material_code=panel_image.material_code).first()
            if material:
                item["material_name"] = material.material_name
                item["failure_map"] = json.loads(material.failure_map) if material.failure_map else ""
            else:
                item["material_name"] = ""
            item["aoi_name"] = session.query(AOI).filter_by(aoi_code=panel_image.aoi_code).first().aoi_name if panel_image.aoi_code else ""

        else:
            item["lot_name"] = ""
            item["lot_code"] = ""
            item["start_time"] = ""
            item["end_time"] = ""
            item["material_name"] = ""
        item["ai_result"] = panel_image.ai_result
        item["judgment"] = panel_image.judgment

        # 复判一致
        if item["ai_result"] == item["judgment"]:
            item["judgment_result"] = 1
        # 复判不一致
        else:
            if item["judgment"] == 0:
                item["judgment_result"] = 0
            # 人工复判：NG, 
            elif item["judgment"] == 2:
                # ai ok
                if item["ai_result"] == 1:
                    item["judgment_result"] = 2
                # ai gray
                elif item["ai_result"] == 3:
                    item["judgment_result"] = 1
            # 人工复判 OK
            else:
                item["judgment_result"] = 2

        failures = session.query(Failure).filter(and_(Failure.del_flag=="0", Failure.failure_id!=0))
        
        item["failure_num"] = 0


        for failure in failures:
            failure_name = failure.failure_name
            # 正确的图片不统计
            if failure_name in ["P4FAKE", "P5FAKE"]:
                continue
            failure_id = failure.failure_id

            item[failure_name] = 0

            for detect_item in detection_info:
                if detect_item["failure_id"] == failure_id:
                    item[failure_name] += 1

            item["failure_num"] += item[failure_name]

        if pattern_aoi:
            item["pattern_aoi"] = {}
            pattern_aoi = session.query(FailureImage.pattern_failure, FailureImage.aoi_failure, func.count().label('aoi_count')).filter(FailureImage.panel_code==panel_image.panel_code).group_by(FailureImage.pattern_failure, FailureImage.aoi_failure).order_by(desc("aoi_count")).all()
            for aoi_item in pattern_aoi:
                pattern_failure = aoi_item.pattern_failure
                aoi_failure = aoi_item.aoi_failure
                aoi_count = aoi_item.aoi_count
                if not item["pattern_aoi"].get(pattern_failure):
                    item["pattern_aoi"][pattern_failure] = []  
                if aoi_failure:
                    item["pattern_aoi"][pattern_failure].append(f"{aoi_failure}({aoi_count})")

        if pattern_ai:
            item["pattern_ai"] = {}
            pattern_ai = session.query(FailureImage.pattern_failure, FailureImage.failure_id, func.count().label('ai_count')).filter(FailureImage.panel_code==panel_image.panel_code).group_by(FailureImage.pattern_failure, FailureImage.failure_id).order_by(desc("ai_count")).all()

            for ai_item in pattern_ai:
                pattern_failure = ai_item.pattern_failure
                failure_id = ai_item.failure_id
                failure_name = session.query(Failure).filter(Failure.failure_id==failure_id).first().failure_name
                ai_count = ai_item.ai_count
                if not item["pattern_ai"].get(pattern_failure):
                    item["pattern_ai"][pattern_failure] = []  

                item["pattern_ai"][pattern_failure].append(f"{failure_name}({ai_count})")

        # 需要查询 sqlserver 获取 lot no
        if lot_no:
            sql_data = check_sqlserver_task(panel_image.panel_id, panel_image.aoi_time)

            item["sql_code"] = sql_data["code"]
            item["lot_no"] = sql_data["lot_no"]

        predict_data.append(item)

    return predict_data


def get_infer_type():
    config = Config()
    config.read(os.path.join(root_path, 'config', 'server.ini'))
    infer_type = config.getint('lot', 'infer_type')

    return infer_type


def lot_code_filter(session, **kwargs):
    lot_codes = []

    lots = lot_filter(session, **kwargs)
    
    for lot in lots.all():
        if lot.lot_code not in lot_codes:
            lot_codes.append(lot.lot_code)

    return lot_codes


def panel_filter(session, **kwargs):
    panel_images = session.query(PanelImage).order_by(PanelImage.aoi_time.desc())

    if kwargs.get("lot_code"):
        panel_images = panel_images.filter(PanelImage.lot_code==kwargs.get("lot_code"))

    else:
        if kwargs.get("lot_name"):
            lot_codes = lot_code_filter(session, lot_name=kwargs.get("lot_name"))
            panel_images = panel_images.filter(PanelImage.lot_code.in_(lot_codes))

    if kwargs.get("aoi_start_time"):
        aoi_start_time = kwargs.get("aoi_start_time")
        aoi_end_time = kwargs.get("aoi_end_time")
        panel_images = panel_images.filter(and_(PanelImage.aoi_time >= aoi_start_time, PanelImage.aoi_time <= aoi_end_time))

    if kwargs.get("panel_id"):
        panel_images = panel_images.filter(PanelImage.panel_id.like("%" + kwargs.get("panel_id") + "%"))

    if kwargs.get("panel_ids"):
        panel_images = panel_images.filter(PanelImage.panel_id.in_(kwargs.get("panel_ids")))

    # 通过 ai 推理的是 ok 还是 ng 过滤
    if kwargs.get("ai_result"):
        panel_images = panel_images.filter(PanelImage.ai_result == kwargs.get("ai_result"))

    if kwargs.get("aoi_result"):
        panel_images = panel_images.filter(PanelImage.aoi_result == kwargs.get("aoi_result"))

    if kwargs.get("start_time"):
        lot_codes = lot_code_filter(session, start_time=kwargs.get("start_time"), end_time=kwargs.get("end_time"))
        panel_images = panel_images.filter(PanelImage.lot_code.in_(lot_codes))

    if kwargs.get("start_time_contain"):
        lot_codes = lot_code_filter(session, start_time_contain=kwargs.get("start_time_contain"), end_time_contain=kwargs.get("end_time_contain"))
        panel_images = panel_images.filter(PanelImage.lot_code.in_(lot_codes))

    if kwargs.get("pjudgment"):
        panel_images = panel_images.filter(PanelImage.judgment == int(kwargs.get("pjudgment")))

    # 过滤人工复判
    if kwargs.get("judgments"):
        conditions = []
        for judgment in kwargs.get("judgments"):
            if judgment == 0:
                conditions.append(PanelImage.judgment == '0')
            elif judgment == 1:
                conditions.append(or_(PanelImage.judgment == PanelImage.ai_result, and_(PanelImage.judgment==2, PanelImage.ai_result==3)))
            elif judgment == 2:
                conditions.append(or_(and_(PanelImage.judgment==1, PanelImage.ai_result==2), and_(PanelImage.judgment==2, PanelImage.ai_result==1), and_(PanelImage.judgment==1, PanelImage.ai_result==3)))

        panel_images = panel_images.filter(or_(*conditions))

    # 通过缺陷类型过滤
    if kwargs.get("failure_ids"):
        # 去重
        panel_codes = session.query(FailureImage.panel_code).filter(FailureImage.failure_id.in_(kwargs.get("failure_ids"))).distinct().all()
        panel_codes = [value for value, in panel_codes]
        panel_images = panel_images.filter(PanelImage.panel_code.in_(panel_codes))

    if kwargs.get("material_names"):
        lot_codes = lot_code_filter(session, material_names=kwargs.get("material_names"))
        panel_images = panel_images.filter(PanelImage.lot_code.in_(lot_codes))

    if kwargs.get("material_name"):
        lot_codes = lot_code_filter(session, material_name=kwargs.get("material_name"))
        panel_images = panel_images.filter(PanelImage.lot_code.in_(lot_codes))

    if kwargs.get("server_hosts"):
        panel_images = panel_images.filter(PanelImage.server_host.in_(kwargs.get("server_hosts")))

    return panel_images


def lot_filter(session, **kwargs):
    lots = session.query(Lot)

    if kwargs.get("lot_names"):
        lot_names = kwargs.get("lot_names")
        lots = lots.filter(Lot.lot_name.in_(lot_names))

    if kwargs.get("lot_name"):
        lots = lots.filter(Lot.lot_name.like("%" + kwargs.get("lot_name") + "%"))

        # for lot in lots.all():
        #     print(lot.lot_name)

    # 时间必须包含在里面才可以查询
    if kwargs.get("start_time_contain"):
        start_time = kwargs.get("start_time_contain")
        end_time = kwargs.get("end_time_contain")
        lots = lots.filter(Lot.start_time >= start_time).filter(Lot.end_time <= end_time, Lot.end_time.isnot(None))

    # 前端选择的时间和数据库的时间会相差 8 个时区
    # 时间只要有交叉就可以查询
    if kwargs.get("start_time"):
        # 前端虽然发送的是北京时间，但后端接收到的还是 UTC 时间
        start_time = kwargs.get("start_time")
        end_time = kwargs.get("end_time")

        time_conditions = []
        time_conditions.append(and_(Lot.start_time <= end_time, Lot.start_time >= start_time))
        time_conditions.append(and_(Lot.end_time <= end_time, Lot.end_time >= start_time, Lot.end_time.isnot(None)))
        time_conditions.append(and_(Lot.end_time >= end_time, Lot.start_time <= start_time, Lot.end_time.isnot(None)))

        lots = lots.filter(or_(*time_conditions))

    if kwargs.get("material_names"):
        conditions = []
        for material_name in kwargs.get("material_names"):
            material = session.query(Material).filter_by(material_name=material_name).first()
            if material:
                material_code = material.material_code
                conditions.append(Lot.material_code.like("%" + material_code + "%"))
        if conditions:
            lots = lots.filter(or_(*conditions))
        else:
            lots = lots.filter(Lot.material_code.in_([]))

    if kwargs.get("material_name"):
        material = session.query(Material).filter_by(material_name=material_name).first()
        if material:
            material_code = material.material_code
        else:
            material_code = ""
        lots = lots.filter(Lot.material_code.like("%" + material_code + "%"))

    if kwargs.get("server_hosts"):
        lots = lots.filter(Lot.server_host.in_(kwargs.get("server_hosts")))
    
    return lots


def failure_filter(session, **kwargs):
    failure_images = session.query(FailureImage).order_by(FailureImage.id.desc()).filter(FailureImage.del_flag == '0')

    if kwargs.get("panel_code") and kwargs.get("lot_code"):
        failure_images = failure_images.filter(FailureImage.panel_code==kwargs.get("panel_code"))

    else:
        if kwargs.get("lot_code"):
            failure_images = failure_images.filter(FailureImage.lot_code==kwargs.get("lot_code"))
        
        if kwargs.get("lot_name"):
            lot_codes = lot_code_filter(session, lot_name=kwargs.get("lot_name"))
            failure_images = failure_images.filter(FailureImage.lot_code.in_(lot_codes))

        if kwargs.get("panel_id"):
            panel_codes = panel_code_filter(session, panel_id=kwargs.get("panel_id"))
            failure_images = failure_images.filter(FailureImage.panel_code.in_(panel_codes))

    if kwargs.get("start_time"):
        lot_codes = lot_code_filter(session, start_time=kwargs.get("start_time"), end_time=kwargs.get("end_time"))
        failure_images = failure_images.filter(FailureImage.lot_code.in_(lot_codes))

    if kwargs.get("failure_ids"):
        failure_images = failure_images.filter(FailureImage.failure_id.in_(kwargs.get("failure_ids")))

    if kwargs.get("judgments"):
        panel_codes = panel_code_filter(judgments=kwargs.get("judgments"))
        failure_images = failure_images.filter(FailureImage.panel_code.in_(panel_codes))

    if kwargs.get("server_hosts"):
        failure_images = failure_images.filter(FailureImage.server_host.in_(kwargs.get("server_hosts")))

    return failure_images
    

def panel_code_filter(session, **kwargs):

    panel_codes = []

    panel_images = panel_filter(session, **kwargs)

    for panel_image in panel_images.all():
        panel_codes.append(panel_image.panel_code)

    return panel_codes


def define_error(error_code):
    if (error_code is None) or (error_code == "OK"): 
        return 1
    else:
        return 2

    
def upload_mes_task(lot_codes):
    print(f"lot_codes: {lot_codes}")
    t1 = time.time()
    with SessionLocal() as session:
        earliest_panel_image = session.query(PanelImage).filter(PanelImage.lot_code.in_(lot_codes)).order_by(PanelImage.aoi_time.asc()).first()

        if not earliest_panel_image:
            return {"code": 1, "msg": "当前 lot 无数据"}

        earliest_start_time = earliest_panel_image.start_time - timedelta(days=3)

        logger.info(f"earliest_start_time: {earliest_start_time}")

        # 如果同一个 chip id 有多个值, 只取最新的那一条
        query_sql = f"""SELECT CHIP_ID,  NG_CODE, START_DATE_TIME, WORKER_ID 
                        FROM (SELECT CHIP_ID,  NG_CODE, START_DATE_TIME, WORKER_ID,
                        ROW_NUMBER() OVER (PARTITION BY CHIP_ID ORDER BY START_DATE_TIME DESC) AS rn
                    FROM {oracle_view}
                    WHERE START_DATE_TIME > TO_DATE('{earliest_start_time}', 'YYYY-MM-DD HH24:MI:SS ') and WORKER_ID !='SYSTEM'
                ) subquery
                WHERE rn = 1"""

        logger.info(f"query_sql: {query_sql}")

        try:
            with connect_oracle() as oracle_con:
                with oracle_con.cursor() as oracle_cursor:
                    oracle_cursor.execute(query_sql)
                    rows = oracle_cursor.fetchall()

                    panel_judge_data = {}

                    i = 0
                    for row in rows:
                        chip_id =row[0]
                        
                        aoi_result = row[1]
                        update_time = row[2]
                        update_user = row[3]

                        panel_image = session.query(PanelImage).filter(PanelImage.panel_id==chip_id).filter(PanelImage.lot_code.in_(lot_codes)).order_by(PanelImage.aoi_time.desc()).first()

                        if panel_image:
                            i += 1

                            server_host = panel_image.server_host

                            panel_judge_data[server_host] = [] if server_host not in panel_judge_data else panel_judge_data[server_host]

                            panel_code = panel_image.panel_code
                            judgment = define_error(aoi_result)

                            panel_judge_data[server_host].append([panel_code, judgment])

                    for server_host, panel_judge in panel_judge_data.items():
                        """
                        调用外部 API 创建一个新的帖子。
                        """
                        # 假设我们要调用的外部 API 地址
                        EXTERNAL_API_URL = f"http://{server_host}:5003/api/lot/upload-mes"

                        logger.info(f"发送请求：http://{server_host}:5003/api/lot/upload-mes")

                        try:
                            payload = {"judge_info": panel_judge}
                            
                            response = requests.post(EXTERNAL_API_URL, json=payload)

                            logger.info(f"外部 API 的 response: {response}")

                            # 检查响应状态码是否为成功
                            if response.status_code != 200:
                                raise HTTPException(status_code=response.status_code, detail="Failed to create post on external API")
                        
                        except Exception as e:
                            # 处理请求错误
                            raise HTTPException(status_code=500, detail=f"An error occurred while requesting the external API: {str(e)}")
                    # 返回外部 API 的响应
                    t2 = time.time()
                    logger.info(f"更新 mes 总耗时：{t2-t1} s")

                    msg = f"更新 mes 总耗时：{t2-t1} s, 处理 panel 个数 {i}"
                    async_msg(msg, level="info")

                    return i
        
        except Exception:
            return {"code": 1, "msg": "MES 数据库连接失败"}
        


def check_sqlserver_task(chip_id, aoi_time):
    start_time = aoi_time - timedelta(minutes=2)
    end_time =aoi_time + timedelta(minutes=2)

    query = f"SELECT TOP 1 ChipID, LotNo, EquipNo, DetectionTime FROM DetectHistory Where ChipID='{chip_id}' and DetectionTime>'{start_time}' and DetectionTime<'{end_time}' ORDER BY DetectionTime DESC;"

    lot_no = None

    try:
        with connect_sqlserver() as sql_con:
            with sql_con.cursor() as sql_cursor:
                sql_cursor.execute(query)

                # 获取结果
                # rows = sql_cursor.fetchall()
                # for row in rows:
                #     print(row)

                row = sql_cursor.fetchone()
                if row:
                    lot_no = row[1]
                    print(f"ChipID: {row[0]}, LotNo: {row[1]}, EquipNo: {row[2]}, DetectionTime: {row[3]}")
                return {"code": 0, "lot_no": lot_no}
    except pymssql.Error as e:
        return {"code": 1, "msg": "SqlServer 数据库连接失败", "lot_no": lot_no}

        
def get_lots_job(lot_name, is_complete):
    res = {}

    with SessionLocal() as session:
        if lot_name:
            lots = session.query(Lot).filter(Lot.lot_name.like("%" + lot_name + "%")).order_by(Lot.start_time.desc())
        else:
            lots = session.query(Lot).order_by(Lot.start_time.desc())

        if is_complete:
            lots = lots.filter(Lot.status == '1')

        lot_data = []

        for lot in lots.all():
            panel_num = session.query(func.count(PanelImage.id)).filter(PanelImage.lot_code == lot.lot_code).scalar()

            # 只更新最新的 lot, 重名的 lot 不做
            if (lot.lot_name in lot_data) or (panel_num==0):
                continue

            aoi_name = get_aoi_name(lot.aoi_code) if lot.aoi_code else ""

            ai = session.query(AI).filter(AI.hostname==lot.server_host).first()
            ai_name = ai.ai_name

            if is_complete:
                lot_data.append({"lotname": lot.lot_name, "start_time": lot.start_time.strftime('%Y-%m-%d %H:%M:%S'), "end_time": lot.end_time.strftime('%Y-%m-%d %H:%M:%S'), "lot_code": lot.lot_code, "ai_name": ai_name, "aoi_name": aoi_name})
            else:
                lot_data.append({"lotname": lot.lot_name, "lot_code": lot.lot_code})

        res["data"] = lot_data

        return res
        